﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PortalLogReport.Models;
using PortalLogReport.Repositories;
using System.Data;
using PortalLogReport.Common;
using Vertica.Data.VerticaClient;
using System.Text;

namespace PortalLogReport.Repositories
{
    public class CategoryRepository : IResultRepository
    {
        #region Repository Properties

        public KeyValuePair<ChartCommon.CategoryTypes, string> PairCategoryTypes  { get; set; }
        public ChartCommon.Interval IntervalType { get; set; }
        public string Level1 { get; set; }
        public string Level2 { get; set; }

        #endregion;

        #region Constructor
        public CategoryRepository(KeyValuePair<string, string> subDimensionType)
        {
            PairCategoryTypes = new KeyValuePair<ChartCommon.CategoryTypes, string>(
                (ChartCommon.CategoryTypes)Enum.Parse(typeof(ChartCommon.CategoryTypes), subDimensionType.Key), subDimensionType.Value);
        }

        #endregion

        #region Repository Methods

        public IList<BaseChartModel> GetResults(DateTime startDate, DateTime endDate)
        {
            return null;
        }

        public BaseChartModel GetResult(DateTime startDate, DateTime endDate)
        {
            StringBuilder commText = new StringBuilder();
            commText.AppendLine(@"select groupby, dimension , count(dimension) result from (");
            commText.AppendLine(BuildSelectSQL());
            commText.AppendLine("from public.PORTAL_LOG where severity='Error'" +  BuildWhereSQL() + " ) a ");
            commText.AppendLine("group by groupby, dimension");

            VerticaParameter[] verticaParams = new VerticaParameter[2];
            verticaParams[0] = new VerticaParameter("startDate", VerticaType.DateTime, startDate);
            verticaParams[1] = new VerticaParameter("endDate", VerticaType.DateTime, endDate);

            DataSet ds = VerticalHelper.ExecuteDataset(CommandType.Text, commText.ToString(), verticaParams);

            BaseChartModel baseChartModel = new BaseChartModel();

            Dictionary<string, Dictionary<string, int>> dataModel = new Dictionary<string, Dictionary<string, int>>();

            if (ds != null && ds.Tables.Count > 0)
            {
                if (IntervalType == ChartCommon.Interval.None)
                {
                    dataModel = ds.Tables[0].AsEnumerable().OrderBy(x => x["groupby"]).OrderByDescending(x => x["result"]).GroupBy(x => x["groupby"].ToString()).
                                         ToDictionary(g => g.Key.ToString(), g => g.GroupBy(p => p["dimension"].ToString()).
                                             ToDictionary(x => x.Key.ToString(), x => Convert.ToInt32(x.First()["result"])));
                }
                else
                {
                    dataModel = ds.Tables[0].AsEnumerable().OrderBy(x => x["groupby"]).GroupBy(x => x["groupby"].ToString()).
                                     ToDictionary(g => g.Key.ToString(), g => g.GroupBy(p => p["dimension"].ToString()).
                                         ToDictionary(x => x.Key.ToString(), x => Convert.ToInt32(x.First()["result"])));
                }
            }

            return GetTop10Results(baseChartModel, dataModel);
        }

        private BaseChartModel GetTop10Results(BaseChartModel model, Dictionary<string, Dictionary<string, int>> dataModel)
        {
            model.BaseModel = new Dictionary<string, Dictionary<string, int>>();

            var enumerator = dataModel.GetEnumerator();
            while (enumerator.MoveNext())
            {
                var dataResults = enumerator.Current.Value.AsEnumerable().OrderByDescending(x => x.Value).Take(10).ToDictionary(k => k.Key, k => k.Value);
                model.BaseModel.Add(enumerator.Current.Key, dataResults);
            }

            return model;
        }


        public string BuildSelectSQL()
        {
            StringBuilder sqlString = new StringBuilder();

            sqlString.AppendLine("select ");

            if (PairCategoryTypes.Key == ChartCommon.CategoryTypes.All)
            {
                //when appdomainname like '%/AW000003%' then 'Put-Thru'
                //when appdomainname like '%/AW000026%' then 'Put-Thru'
                //when appdomainname like '%/Finance%' then 'Put-Thru'
                //when appdomainname like '%/pyrConfig%' then 'Put-Thru'
                //when appdomainname like '%/SFDCPortlets%' then 'SFDC'
                //when appdomainname like '%/CustomerPortal%' then 'CustomerPortal'
                //when appdomainname like '%/AW000019%' then 'AssetEdge'
                //when appdomainname like '%/BPMTask%' then 'Common'
                //when appdomainname like '%/DocumentsAW%' then 'Common'
                //when appdomainname like '%/GUARD%' then 'Common'
                //when appdomainname like '%/ObligationMgmt%' then 'Common'
                //when appdomainname like '%/Reports%' then 'Common'


                sqlString.AppendLine(@" case
                        when appdomainname like '%/AW000004%' then 'PutOn'
                        when appdomainname like '%/AW000021%' then 'PutOn'
                        when appdomainname like '%/AW000028%' then 'PutOn'
                        when appdomainname like '%/CIM%' then 'PutOn'
                        when appdomainname like '%/global%' then 'PutOn'

                        when appdomainname like '%/AW000020%' then 'TVS'
                        when appdomainname like '%/AW000027%' then 'TVS'
                        when appdomainname like '%/AW000033%' then 'TVS'
                        ");
                sqlString.AppendLine(@"when appdomainname ='PricingEngine' then 'PutOn'
                        when appdomainname ='CustomerPortalMVC' then 'CP'
                        when appdomainname ='PartnerPortalMVC' then 'PC'
                        when appdomainname ='PPUnhandledException' then 'PC'
                        ");
                sqlString.AppendLine(@"when appdomainname ='ALD Async Runner' then 'Service'
                        when appdomainname ='Async Runner' then 'Service'
                        when appdomainname ='BEM' then 'Service'
                        when appdomainname ='CustomerPortal Async Runner' then 'Service'
                        when appdomainname ='Flexi Async Runner' then 'Service'
                        when appdomainname ='HPFS AutoBooking Pipeline' then 'Service'
                        when appdomainname ='HPFS Customer Portal Asset Request Service' then 'Service'
                        when appdomainname ='HPFS Siebel Load' then 'Service'
                        when appdomainname ='HPFS UploadToTrimService' then 'Service'
                        when appdomainname ='HPFS Vendor AsyncRunner' then 'Service'
                        when appdomainname ='HPFS.Enterprise.GPO.Document.Service' then 'Service'
                        when appdomainname ='HPFS.Enterprise.GPO.DocuSignConnectListener' then 'Service'
                        when appdomainname ='Insurance Async Runner' then 'Service'
                        when appdomainname ='Invoicing Async Runner' then 'Service'
                        when appdomainname ='InvoicingAsyncRunner' then 'Service'
                        when appdomainname ='InvoicingAsyncRunner.exe' then 'Service'
                        when appdomainname ='PartnerPortalCreditService' then 'Service'
                        when appdomainname ='Phx Async Runner' then 'Service'
                        when appdomainname ='PhxAsyncRunner.exe' then 'Service'
                        when appdomainname ='PricingQuoteService' then 'Service'
                        when appdomainname ='Pyramid Async Runner' then 'Service'
                        when appdomainname ='Run_SSIS_Package.exe' then 'Service'
                        when appdomainname ='SiebelLoad' then 'Service'
                        when appdomainname ='SSIS' then 'Service'
                        when appdomainname ='TVS Async Runner' then 'Service'
                        when appdomainname ='UploadToTrimService.exe' then 'Service'
                        when appdomainname ='WCF Customer Portal Report Service' then 'Service'
                        when appdomainname ='WCFAsyncRunner' then 'Service'
                        when appdomainname ='WCFBulkupLoadService' then 'Service'
                        when appdomainname ='WCFCustomerNotificationService' then 'Service'
                        when appdomainname ='WCFCustomerPortalReportService' then 'Service'
                        when appdomainname ='WCFCustomerPortalService' then 'Service'
                        when appdomainname ='WCFDataMaintenanceService' then 'Service'
                        when appdomainname ='WCFDocumentGenerationService' then 'Service'
                        when appdomainname ='WCFDocumentService' then 'Service'
                        when appdomainname ='WCFPartnerPortalService' then 'Service'
                        when appdomainname ='WCFPyramidService' then 'Service'
                        when appdomainname ='WCFQualiSoftService' then 'Service'
                        ");
            }
            else
            {
                Type subAppTypeEnum = typeof(ChartCommon).Assembly.GetType("PortalLogReport.Common.ChartCommon+" + PairCategoryTypes.Key.ToString() + "ApplicationTypes", true, true);

                int i = 1;

                if (PairCategoryTypes.Key == ChartCommon.CategoryTypes.Service || PairCategoryTypes.Key == ChartCommon.CategoryTypes.CP || PairCategoryTypes.Key == ChartCommon.CategoryTypes.PC)
                {
                    foreach (var item in Enum.GetValues(subAppTypeEnum))
                    {
                        if (i++ == 1)
                            sqlString.AppendLine(@"case when UPPER(REPLACE(REPLACE(appdomainname,' ',''),'.','')) = '" + item.ToString().ToUpper() + "' then '" + item.ToString() + "'");
                        else
                            sqlString.AppendLine(@"when UPPER(REPLACE(REPLACE(appdomainname,' ',''),'.','')) = '" + item.ToString().ToUpper() + "' then '" + item.ToString() + "'");
                    }
                }
                else
                {
                    foreach (var item in Enum.GetValues(subAppTypeEnum))
                    {
                        if (i++ == 1)
                            sqlString.AppendLine(@"case when UPPER(REPLACE(REPLACE(appdomainname,' ',''),'.','')) like '%/" + item.ToString().ToUpper() + "%' then '" + item.ToString() + "'");
                        else
                            sqlString.AppendLine(@"when UPPER(REPLACE(REPLACE(appdomainname,' ',''),'.','')) like '%/" + item.ToString().ToUpper() + "%' then '" + item.ToString() + "'");
                    }
                }
            }

            sqlString.AppendLine("else 'Others' end dimension ,");
            switch (this.IntervalType)
            {
                case ChartCommon.Interval.Month:
                    sqlString.AppendLine("to_char(year(timestamp),'9999') || '/' || to_char(month(timestamp),'00') as groupby ");
                    break;
                case ChartCommon.Interval.Week:
                    sqlString.AppendLine("to_char(year(timestamp),'9999') || '/' || to_char(week(timestamp),'00') as groupby ");
                    break;
                case ChartCommon.Interval.Year:
                    sqlString.AppendLine("to_char(year(timestamp),'9999') as groupby ");
                    break;
                default:
                    sqlString.AppendLine("'' as groupby ");
                    break;
            }

            return sqlString.ToString();
        }

        public string BuildWhereSQL()
        {
            StringBuilder sqlString = new StringBuilder();

            if (PairCategoryTypes.Key != ChartCommon.CategoryTypes.All)
            {

                Type subAppTypeEnum = typeof(ChartCommon).Assembly.GetType("PortalLogReport.Common.ChartCommon+" + PairCategoryTypes.Key.ToString() + "ApplicationTypes");

                if (PairCategoryTypes.Key == ChartCommon.CategoryTypes.Service || PairCategoryTypes.Key == ChartCommon.CategoryTypes.CP || PairCategoryTypes.Key == ChartCommon.CategoryTypes.PC)
                {
                    sqlString.AppendLine(" and UPPER(REPLACE(REPLACE(appdomainname,' ',''),'.','')) in (");

                    foreach (var item in Enum.GetValues(subAppTypeEnum))
                    {
                        if (!item.ToString().Equals("All"))
                            sqlString.Append(@"'" + item.ToString().ToUpper() + "',");
                    }

                    sqlString.Remove(sqlString.Length - 1, 1);
                    sqlString.Append(")");
                }
                else
                {
                    sqlString.Append(" and REGEXP_LIKE(upper(appdomainname), '");

                    int i = 1;
                    foreach (var item in Enum.GetValues(subAppTypeEnum))
                    {
                        if (!item.ToString().Equals("All"))
                        {
                            if (i++ == 1)
                                sqlString.Append("/" + item.ToString().ToUpper());
                            else
                                sqlString.Append("|/" + item.ToString().ToUpper());
                        }
                    }

                    sqlString.Append("')");
                }
            }

            sqlString.Append(" and timestamp>=@startDate and timestamp<@endDate");
            return sqlString.ToString();
        }

        #endregion
    }
}
